import numpy as np
import pandas as pd
import pymysql
import pymysql.cursors

class MysqlUtils:
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )
    
    def get_scenic_data(self):
        """从数据库获取原始数据并进行预处理"""
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        
        sql = """  
        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code, 
               DAYOFWEEK(gate.create_time) as non_weekend, 
               cast(substring(rel.id_no, 7, 4) as unsigned) as birth_year 
        FROM ticket_order_user_rel rel  
        LEFT JOIN ticket_order t on t.id = rel.order_id  
        LEFT JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id 
        WHERE t.pay_time != '' and t.tourist_agency_name != ''
        """  
        
        cursor.execute(sql)  
        ret = cursor.fetchall()  
        df = pd.DataFrame(ret)
        
        # 数据清洗
        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() != '' else 0)
        
        # 特征工程
        df['out_province_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and x['province_code'] != 44) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        
        # 分组聚合计算指标
        result = df.groupby('tourist_agency_name').agg(
        total_visitors=('id_no', 'count'),      # 总游客数
        valid_visitors=('valid_id', 'sum'),     # 有效身份证游客数
        out_province=('out_province_ratio', 'sum'),  # 外省人数（有效id_no）
        elderly=('elderly_ratio', 'sum'),       # 老年人人数
        non_weekend=('non_weekend', 'mean')    # 非周末占比
    ).reset_index()
        
def calculate_ratios(df):
   
    # 分组聚合计算
    result = df.groupby('tourist_agency_name').agg(
        total_visitors=('id_no', 'count'), # 总前签数
        valid_visitors=('valid_id', 'sum'), # yc1'
        total_visitors=('id_no', 'count'), # xyou
        total_visitors=('id_no', 'count'), # 上表 2.又 3.由 4.发 5.you ( )↔
        total_visitors=('id_no', 'count'), # 总前签数
        total_visitors=('id_no', 'count'), # 总前签数
        out_province=('out_province_ratio', 'sum'), # 外省人数（有效id_no)
        elderly=('elderly_ratio', 'sum'), # 老年人人数
        non_weekend=('non_weekend', 'mean'), # 非周末占比
    ).reset_index()

    result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0, np.nan)
    result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0, np.nan)

# 清除中间列
    result = result.drop(['out_province_ratio', 'elderly'])
    result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
    result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
    print(result.head)


if __name__ == '__main__':
    mu = MysqlUtils()
    scenic_data = mu.get_scenic_data()
    scenic_data.to_csv('processed_scenic_data.csv', index=False)